CREATE TABLE [dbo].[AIPOImpH](
	[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[Token] [nvarchar](50) NULL,
	[VendorCode] [nvarchar](50) NULL,
	[VendorReferenceNo] [nvarchar](50) NULL,
	[Remarks] [nvarchar](200) NULL,
	[CreatedDate] [datetime] NULL,
	[ErrMsg] [nvarchar](max) NULL,
 CONSTRAINT [PK_AI_PO_Imp_H] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[AIPOImpL](
	[ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[HeaderID] [numeric](18, 0) NULL,
	[ItemCode] [nvarchar](50) NULL,
	[Quantity] [numeric](18, 6) NULL,
	[Warehouse] [nvarchar](50) NULL,
 CONSTRAINT [PK_AI_PO_L] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO



create proc sp_AI_InsertH
@Token nvarchar(50),
@VendorCode nvarchar(50),
@RefNo nvarchar(50),
@Remarks nvarchar(200)
as
Insert into AIPOImpH 
select @Token,@VendorCode,@RefNo,@Remarks,GETDATE(),'' 

select @@IDENTITY ID


create proc sp_AI_InsertL
@ID numeric(18,0),
@ItemCode nvarchar(50),
@Quantity numeric(19,0),
@Warehouse nvarchar(10)
as
insert into AIPOImpL
select @ID,@ItemCode,@Quantity,@Warehouse

create proc sp_AI_UpdateLog
@ID numeric(18,0),
@ErrMsg nvarchar(max)
as
update AIPOImpH set ErrMsg=@ErrMsg where ID=@ID


alter proc sp_AI_ImpPOEmail 
@Token as nvarchar(100)
as
select T0.docdate,T1.WhsCode, sum(T1.LineTotal) LineTotal, sum(T1.GTotal) GTotal
from OPOR T0
join POR1 T1 on T0.DocEntry=T1.DocEntry
where isnull(T0.U_POSTxNo,'')=@Token

group by T1.WhsCode,T0.docdate
